﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;


namespace W1
{
    class DBHelper
    {
            public static string str;
            public static string connString = "server=.;database=banjiguanli;Integrated Security=true";
            //数据库连接对象
            public static SqlConnection conn = null;
            //初始化数据库连接
            public static void InitConnection()
            {
                //如果连接对象不存在，则创建连接
                if (conn == null)
                {
                    conn = new SqlConnection(connString);
                }
                //如果连接对象关闭，则打开连接
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }
                //如果连接中断，则重启连接
                if (conn.State == ConnectionState.Broken)
                {
                    conn.Open();
                    conn.Close();
                }
            }
            //查询
            public static SqlDataReader GetDataReader(string sqlStr)
            {
                InitConnection();
                SqlCommand cmd = new SqlCommand(sqlStr, conn);
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);

            }
            //增删改
            public static bool ExecuteNonQuery(string sqlStr)
            {
                InitConnection();
                SqlCommand cmd = new SqlCommand(sqlStr, conn);
                int result = cmd.ExecuteNonQuery();
                conn.Close();
                return result > 0;
            }
            //执行集合函数
            public static object ExecuteScalar(string sqlStr)
            {
                InitConnection();
                SqlCommand cmd = new SqlCommand(sqlStr, conn);
                object result = cmd.ExecuteScalar();
                conn.Close();
                return result;
          

            }
        public static DataTable SelectByAdapter(string sql)
        {
            //1. 创建数据库连接对象Connection
            SqlConnection conn = new SqlConnection(connString);
            //2. 创建数据适配对象DataAdapter
            SqlDataAdapter da = new SqlDataAdapter(sql, conn);
            //3. 创建数据集DataSet或DataTable对象，用于缓存数据
            DataTable dt = new DataTable();
            //4. 调用DataAdapter对象的Fill()方法填充DataTable对象
            da.Fill(dt);
            //5. 绑定数据源到ListView或DataGridView等数据控件上
            return dt;
        }


    }
}
